﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using itau.im.entidade;
using EnterpriseLibraryWrapper;
using System.Data;

namespace itau.im.dado
{
    public class PricingDB : IPricingDAO
    {
        public List<Serie> ObterSeriesPorCodigoFamilia(int codigoFamilia)
        {
            List<Serie> lista = new List<Serie>();
            IDataAccessWrapper data = null;
            DataSet ds;

            try
            {
                data = EnterpriseLibraryFactory.GetDataAccess();

                data.ParameterIn.Add(new ParameterInWrapper("@COD_FAMI", codigoFamilia, DbType.Int32));

                ds = data.ExecuteDataSet("DB2IM.IM0S6P");

                if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow linha in ds.Tables[0].Rows)
                    {
                        lista.Add(EntidadeFactory.getObterSerie(linha));
                    }
                }

                return lista;
            }
            catch (DadosException ex)
            {
                EnterpriseLibraryFactory.GetExceptionHandler().HandleException(ex, "ErrosDados");
            }
            catch (Exception ex)
            {
                EnterpriseLibraryFactory.GetExceptionHandler().HandleException(ex, "ErrosDados");
            }
            finally
            {
                lista = null;
                data.Dispose();
                data = null;
                ds = null;
            }

            return null;
        }

        public List<Serie> ObterSeriesPorNomeSerie(string nomeSerie)
        {
            List<Serie> lista = new List<Serie>();
            IDataAccessWrapper data = null;
            DataSet ds;

            try
            {
                data = EnterpriseLibraryFactory.GetDataAccess();

                data.ParameterIn.Add(new ParameterInWrapper("@NOM_SRIE", nomeSerie, DbType.String));

                ds = data.ExecuteDataSet("DB2IM.IM0S6Q");

                if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow linha in ds.Tables[0].Rows)
                    {
                        lista.Add(EntidadeFactory.getObterSerie(linha));
                    }
                }

                return lista;
            }
            catch (DadosException ex)
            {
                EnterpriseLibraryFactory.GetExceptionHandler().HandleException(ex, "ErrosDados");
            }
            catch (Exception ex)
            {
                EnterpriseLibraryFactory.GetExceptionHandler().HandleException(ex, "ErrosDados");
            }
            finally
            {
                lista = null;
                data.Dispose();
                data = null;
                ds = null;
            }

            return null;
        }

        public List<Familia> ObterFamiliaPorCodigoFamilia(int codigoFamilia)
        {
            List<Familia> lista = new List<Familia>();
            IDataAccessWrapper data = null;
            DataSet ds;

            try
            {
                data = EnterpriseLibraryFactory.GetDataAccess();

                data.ParameterIn.Add(new ParameterInWrapper("@COD_FAMI", codigoFamilia, DbType.String));
                data.ParameterOut.Add(new ParameterOutWrapper("@DES_FAMI", DbType.String, 150));
                data.ParameterOut.Add(new ParameterOutWrapper("@NOM_FAMI", DbType.String, 150));
                data.ParameterOut.Add(new ParameterOutWrapper("@IND_RGTO_ATIV", DbType.Int16, 2));

                ds = data.ExecuteParameterToDataSet("DB2IM.IM0S6R");  

                if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow linha in ds.Tables[0].Rows)
                    {
                        lista.Add(EntidadeFactory.getObterFamilia(linha));
                    }
                }

                return lista;
            }
            catch (DadosException ex)
            {
                EnterpriseLibraryFactory.GetExceptionHandler().HandleException(ex, "ErrosDados");
            }
            catch (Exception ex)
            {
                EnterpriseLibraryFactory.GetExceptionHandler().HandleException(ex, "ErrosDados");
            }
            finally
            {
                lista = null;
                data.Dispose();
                data = null;
                ds = null;
            }

            return null;
        }

        public List<Familia> ObterFamiliaPorNomeFamilia(string nomeFamilia)
        {
            List<Familia> lista = new List<Familia>();
            IDataAccessWrapper data = null;
            DataSet ds;

            try
            {
                data = EnterpriseLibraryFactory.GetDataAccess();

                data.ParameterIn.Add(new ParameterInWrapper("@NOM_FAMI", nomeFamilia, DbType.String));

                ds = data.ExecuteDataSet("DB2IM.IM0S6S");

                if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow linha in ds.Tables[0].Rows)
                    {
                        lista.Add(EntidadeFactory.getObterFamilia(linha));
                    }
                }

                return lista;
            }
            catch (DadosException ex)
            {
                EnterpriseLibraryFactory.GetExceptionHandler().HandleException(ex, "ErrosDados");
            }
            catch (Exception ex)
            {
                EnterpriseLibraryFactory.GetExceptionHandler().HandleException(ex, "ErrosDados");
            }
            finally
            {
                lista = null;
                data.Dispose();
                data = null;
                ds = null;
            }

            return null;
        }

        public List<Atributo> ObterAtributosPorCodigoFamiliaCodigoTipoAtributo(int codigoFamilia, Int16 codigoTipoAtributo, Int16 codigoTipoDadoAtributo)
        {
            List<Atributo> lista = new List<Atributo>();
            IDataAccessWrapper data = null;
            DataSet ds;

            try
            {
                data = EnterpriseLibraryFactory.GetDataAccess();

                data.ParameterIn.Add(new ParameterInWrapper("@COD_FAMI", codigoFamilia, DbType.Int32));
                data.ParameterIn.Add(new ParameterInWrapper("@COD_TIPO_ATBT", codigoTipoAtributo, DbType.Int16));
                data.ParameterIn.Add(new ParameterInWrapper("@COD_TIPO_DADO_ATBT", codigoTipoDadoAtributo, DbType.Int16));

                ds = data.ExecuteDataSet("DB2IM.IMS4W");

                if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow linha in ds.Tables[0].Rows)
                    {
                        lista.Add(EntidadeFactory.getObterAtributo(linha));
                    }
                }

                return lista;
            }
            catch (DadosException ex)
            {
                EnterpriseLibraryFactory.GetExceptionHandler().HandleException(ex, "ErrosDados");
            }
            catch (Exception ex)
            {
                EnterpriseLibraryFactory.GetExceptionHandler().HandleException(ex, "ErrosDados");
            }
            finally
            {
                lista = null;
                data.Dispose();
                data = null;
                ds = null;
            }

            return null;
        }

        public List<Serie> ObterSeriesPorCodigoSerie(int codigoSerie)
        {
            List<Serie> lista = new List<Serie>();
            IDataAccessWrapper data = null;
            DataSet ds;

            try
            {
                data = EnterpriseLibraryFactory.GetDataAccess();

                data.ParameterIn.Add(new ParameterInWrapper("@COD_SRIE", codigoSerie, DbType.Int32));

                ds = data.ExecuteDataSet("DB2IM.IM0S7C");

                if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow linha in ds.Tables[0].Rows)
                    {
                        lista.Add(EntidadeFactory.getObterSeriePorCodigoSerie(linha));
                    }
                    lista[0].CodigoSerie = codigoSerie;
                }

                return lista;
            }
            catch (DadosException ex)
            {
                EnterpriseLibraryFactory.GetExceptionHandler().HandleException(ex, "ErrosDados");
            }
            catch (Exception ex)
            {
                EnterpriseLibraryFactory.GetExceptionHandler().HandleException(ex, "ErrosDados");
            }
            finally
            {
                lista = null;
                data.Dispose();
                data = null;
                ds = null;
            }

            return null;
        }

        public List<Familia> ObterFamilias()
        {
            List<Familia> lista = new List<Familia>();
            IDataAccessWrapper data = null;
            DataSet ds;

            try
            {
                data = EnterpriseLibraryFactory.GetDataAccess();

                ds = data.ExecuteDataSet("DB2IM.IM0S7B");

                if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow linha in ds.Tables[0].Rows)
                    {
                        lista.Add(EntidadeFactory.getObterFamilia(linha));
                    }
                }

                return lista;
            }
            catch (DadosException ex)
            {
                EnterpriseLibraryFactory.GetExceptionHandler().HandleException(ex, "ErrosDados");
            }
            catch (Exception ex)
            {
                EnterpriseLibraryFactory.GetExceptionHandler().HandleException(ex, "ErrosDados");
            }
            finally
            {
                lista = null;
                data.Dispose();
                data = null;
                ds = null;
            }

            return null;
        }

        public string ObterValorSerieAtributo(int codigoFamilia, int codigoSerieIntegrante, int codigoAtributoIntegrante, int codigoTipoAtributo, DateTime dataHoraExecucao)
        {
            IDataAccessWrapper data = null;

            try
            {
                data = EnterpriseLibraryFactory.GetDataAccess();

                data.ParameterIn.Add(new ParameterInWrapper("@COD_SRIE", codigoSerieIntegrante, DbType.Int32));
                //data.ParameterIn.Add(new ParameterInWrapper("@COD_FAMI", codigoFamilia, DbType.Int32));
                data.ParameterIn.Add(new ParameterInWrapper("@COD_ATBT", codigoAtributoIntegrante, DbType.Int32));
                data.ParameterIn.Add(new ParameterInWrapper("@COD_TIPO_ATBT", codigoTipoAtributo, DbType.Int16));
                data.ParameterIn.Add(new ParameterInWrapper("@DAT_VIGE_ATBT", dataHoraExecucao, DbType.Date));
                // TODO descomentar o parametro acima
                DataSet ds = data.ExecuteDataSet("DB2IM.IM0S4X");

                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    return ds.Tables[0].Rows[0]["DES_VLR_ATBT"].ToString();
                }
            }
            catch (DadosException ex)
            {
                EnterpriseLibraryFactory.GetExceptionHandler().HandleException(ex, "ErrosDados");
            }
            catch (Exception ex)
            {
                EnterpriseLibraryFactory.GetExceptionHandler().HandleException(ex, "ErrosDados");
            }
            finally
            {
                data.Dispose();
                data = null;
            }

            return string.Empty;
        }
    }
}
